package j.util.jdbc.simple.core;

import j.util.New;
import j.util.StringUtils;
import j.util.jdbc.simple.core.operations.SimpleJdbcInsertOperations;
import j.util.jdbc.simple.core.support.SimpleJdbcUtils;
import j.util.jdbc.simple.metadata.TableMetaData;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public final class SimpleJdbcInsert extends SimpleJdbcAbstract implements SimpleJdbcInsertOperations {

	@Override
	public <T> int insert(Connection connection,Class<T> clazz,Map<String,Object> args)throws SQLException {
		SQLData sd = getSqlData(clazz);
		SQLForInsert sfi = sd.getSqlInsert();
		List<String> insertOrder = New.list(args.size());
		String iSql = sfi.getInsertSql(args,insertOrder);
		String msg = "execute insert dml sql:"+iSql+" with values:"+args;
		//以jdbc方式执行insert操作
        if(!checkCorrected(sd, connection,args)){
            return -1;
        }
        boolean isAutoCommit = connection.getAutoCommit();
        if(isAutoCommit){
            connection.setAutoCommit(false);
        }
        PreparedStatement pstmt;
        TableMetaData tmd = sd.getTmd();
        if(tmd.isTree()){
            //这是树表的增加，先获取增加的节点的父节点下的子节点的最大右值
            log.debug("这是树表，需要正确计算树表的左右值...");
            String sql = String.format("select max(%s) from %s where %s=?",
                    tmd.getRightFieldName(),tmd.getTableName(),tmd.getParentFieldName());
            pstmt = connection.prepareStatement(sql);
            String pkId = String.valueOf(args.get(tmd.getParentFieldName().toUpperCase()));
            pstmt.setString(1, pkId);
            ResultSet rs = pstmt.executeQuery();
            int maxRgt = 0;
            if(rs.next()){
                maxRgt = rs.getInt(1);
            }
            if(maxRgt==0){
                log.debug("当前新增节点的父节点之下没有任何子节点，获取父节点的左节点做为新节点的左节点...");
                //说明此节点下还没有子节点，则获取父节点的左节点做为开始子节点的左值
                String pkFieldName = tmd.getPkColumns();
                sql = String.format("select leftValue from %s where %s=?",
                        tmd.getTableName(),pkFieldName)   ;
                pstmt = connection.prepareStatement(sql);
                pstmt.setString(1,pkId);
                rs = pstmt.executeQuery();
                if(rs.next()){
                    maxRgt = rs.getInt(1);
                }
            }
            if(maxRgt>0){
                log.debug("计算新增节点的左值为"+maxRgt+",更新所有右值大于"+maxRgt+"的节点的右值+2，同时所有左值大于"+maxRgt+"的节点的左值+2");
                //处理后续步骤
                /**
                 * update usr_dept set rgt = rgt +2 where rgt>6;
                 update usr_dept set lft = lft+2 where lft>6;
                 */
                sql = String.format("update %s set %s = %s +2 where %s>?;update %s set %s = %s+2 where %s>?;",
                        tmd.getTableName(),tmd.getRightFieldName(),tmd.getRightFieldName(),tmd.getRightFieldName(),
                        tmd.getTableName(),tmd.getLeftFieldName(),tmd.getLeftFieldName(),tmd.getLeftFieldName());
                pstmt = connection.prepareStatement(sql);
                pstmt.setInt(1,maxRgt);
                pstmt.setInt(2,maxRgt);
                pstmt.execute();
                log.debug("设置新增节点的左值为"+maxRgt+1+",右值为"+maxRgt+2);
                args.put(tmd.getLeftFieldName().toUpperCase(), maxRgt + 1);
                args.put(tmd.getRightFieldName().toUpperCase(),maxRgt+1+1);
            }
        }
        //开始增加
        pstmt = connection.prepareStatement(iSql);
        SimpleJdbcUtils.setPreapredStatementParameters(sd.getTmd().getColumns(), pstmt, insertOrder, args);
        int ret = pstmt.executeUpdate();
        log.info(msg);

        pstmt.close();
        if(isAutoCommit){
            connection.commit();
        }
        return ret;
	}
	private boolean checkCorrected(SQLData sd, Connection connection,
			Map<String, Object> args)throws SQLException {
		SQLForInsert sfi = sd.getSqlInsert();
		String cSql = sfi.getCheckedSql();
		//若没有生成cSql，则说明不用检查
		if(StringUtils.isNullOrEmpty(cSql)){
			log.info("no checked.");
			return true;
		}
		log.info("execute checked corredted:"+cSql);
		List<String> checkedOrder = sfi.getCheckedOrder();
        PreparedStatement pstmt = connection.prepareStatement(cSql);
        //设置参数
        SimpleJdbcUtils.setPreapredStatementParameters(sd.getTmd().getColumns(), pstmt, checkedOrder, args);
        ResultSet rs = pstmt.executeQuery();
        int count = 0;
        if(rs.next()){
            count = rs.getInt(1);
            log.info(count == 0 ?"checked ok.":"db has "+count+".");
        }
        pstmt.close();
        rs.close();
        return count==0;
	}
	
	/**
	 * 批量执行插入操作，批量执行时的insert语句是根据参数map中的key所生成的，而不管其是否指定了值
	 */
	@Override
	public <T> int[] insertBatch(Connection connection,Class<T> clazz,Map<String,Object>[] args) throws SQLException{
		SQLData sd = getSqlData(clazz);
		SQLForInsert sfi = sd.getSqlInsert();
		List<String> columnsOrder = New.list();
		//根据map中的key生成插入列表
		//String iSql = sfi.getInsertSqlBySpecificColumns(args[0].keySet(),columnsOrder);
		String iSql = sfi.getInsertSql(args[0], columnsOrder);
		String msg = "execute batch sql:"+iSql;
		log.info("begin "+msg);
		int[] rets = new int[args.length];
		//以jdbc方式执行insert操作
        PreparedStatement pstmt = connection.prepareStatement(iSql);
        //设置执行语句的参数
        for(Map<String,Object> map : args){
            //设置参数
            SimpleJdbcUtils.setPreapredStatementParameters(sd.getTmd().getColumns(), pstmt, columnsOrder, map);
            log.info("add batch with valeus:"+map);
            pstmt.addBatch();
        }
        rets = pstmt.executeBatch();
        log.info("succussed to execute batch insert,"+rets.length+" rows affected.");
        pstmt.close();
        return rets;
	}

	@Override
	public <T> Number insertAndReturnKey(Connection connection,Class<T> clazz,Map<String,Object> args)throws SQLException {
		int ret = 0;
		SQLData sd = getSqlData(clazz);
		SQLForInsert sfi = sd.getSqlInsert();
		List<String> insertOrder = New.list(args.size());
		String iSql = sfi.getInsertSql(args,insertOrder);
		String msg = "execute insertAndReturnKey dml sql:"+iSql+" with values:"+args;
		//以jdbc方式执行insert操作
        if(!checkCorrected(sd, connection,args)){
            return -1;
        }
        PreparedStatement pstmt = connection.prepareStatement(iSql);
        //设置执行语句的参数
        SimpleJdbcUtils.setPreapredStatementParameters(sd.getTmd().getColumns(), pstmt, insertOrder, args);
        pstmt.executeUpdate();
        ResultSet rs = pstmt.getGeneratedKeys();
        if(null != rs && rs.next()){
            ret = rs.getInt(1);
            rs.close();
        }
        log.error(msg+"with return generated key:"+ret);
        pstmt.close();
        return ret;
	}
}
